﻿

using System;
using System.Data;
using Model;
namespace Dal
{
	public class DbUsersDal
	{
		public DbUsersDal()
		{
		}
 		#region Insert
 		public static int Insert(DbUsers dbUsers)
		{	
			string sql = @"Insert Into DbUsers (
					UserName,
					UserPwd,
					FirstName,
					LastName,
					Sex,
					Tel,
					Email,
					Address,
					Address2,
					Status,
					ZipCode,
					AddTime,
					City,
					State,
					Country,
					FirstIP,
					LastIP,
					LastLoginTime,
					TotalCost,
					LoginTimes
				) values('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}',{9},'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',{18},{19})";
			sql = string.Format(sql,
					SqlHelper.String(dbUsers.UserName),
					SqlHelper.String(dbUsers.UserPwd),
					SqlHelper.String(dbUsers.FirstName),
					SqlHelper.String(dbUsers.LastName),
					dbUsers.Sex,
					SqlHelper.String(dbUsers.Tel),
					SqlHelper.String(dbUsers.Email),
					SqlHelper.String(dbUsers.Address),
					SqlHelper.String(dbUsers.Address2),
					dbUsers.Status,
					SqlHelper.String(dbUsers.ZipCode),
					dbUsers.AddTime,
					SqlHelper.String(dbUsers.City),
					SqlHelper.String(dbUsers.State),
					SqlHelper.String(dbUsers.Country),
					SqlHelper.String(dbUsers.FirstIP),
					SqlHelper.String(dbUsers.LastIP),
					dbUsers.LastLoginTime,
					dbUsers.TotalCost,
					dbUsers.LoginTimes
				);
			return SqlHelper.ExecSql(sql);
		}
		#endregion
		
		#region Update
 		public static int Update(DbUsers dbUsers)
		{	
			string sql = @"Update DbUsers set 

					UserName= '{0}',
					UserPwd= '{1}',
					FirstName= '{2}',
					LastName= '{3}',
					Sex= {4},
					Tel= '{5}',
					Email= '{6}',
					Address= '{7}',
					Address2= '{8}',
					Status= {9},
					ZipCode= '{10}',
					AddTime= '{11}',
					City= '{12}',
					State= '{13}',
					Country= '{14}',
					FirstIP= '{15}',
					LastIP= '{16}',
					LastLoginTime= '{17}',
					TotalCost= {18},
					LoginTimes= {19}
				where

					UserID= {20}";
			sql = string.Format(sql,
					SqlHelper.String(dbUsers.UserName),
					SqlHelper.String(dbUsers.UserPwd),
					SqlHelper.String(dbUsers.FirstName),
					SqlHelper.String(dbUsers.LastName),
					dbUsers.Sex,
					SqlHelper.String(dbUsers.Tel),
					SqlHelper.String(dbUsers.Email),
					SqlHelper.String(dbUsers.Address),
					SqlHelper.String(dbUsers.Address2),
					dbUsers.Status,
					SqlHelper.String(dbUsers.ZipCode),
					dbUsers.AddTime,
					SqlHelper.String(dbUsers.City),
					SqlHelper.String(dbUsers.State),
					SqlHelper.String(dbUsers.Country),
					SqlHelper.String(dbUsers.FirstIP),
					SqlHelper.String(dbUsers.LastIP),
					dbUsers.LastLoginTime,
					dbUsers.TotalCost,
					dbUsers.LoginTimes,
					dbUsers.UserID);
			return SqlHelper.ExecSql(sql);
		}
		#endregion
 
 

 		#region Delete
 		public static int Delete(Int32 userID)
		{
		
			string sql = @"Delete from DbUsers where 
					UserID= {0}";
			sql = string.Format(sql,
					userID
);
			return SqlHelper.ExecSql(sql);
		} 
		#endregion
 
 		#region Select
 		
 		public static DataSet Select()
		{
			string sql = "select a.* from DbUsers a "; 
			return SqlHelper.Select(sql);
		}
				
		public static DbUsers Select(Int32 userID)
		{
			DbUsers dbUsers = null;
			string sql = @"select a.* from DbUsers a  where
					a.UserID= {0}
";
			sql = string.Format(sql,
					userID
);
			DataSet ds = SqlHelper.Select(sql);
			if(SqlHelper.HasRow(ds))
				dbUsers = new DbUsers(ds.Tables[0].Rows[0]);					
			return dbUsers;
		}
		
		public static DataSet Select(string where)
		{ 
			if (where.Trim().ToLower().StartsWith("where"))
				where = where.Substring(5);
			if (!where.Trim().ToLower().StartsWith("and") && where.Trim().Length > 0)
				where = " and " + where;
			string sql = "select a.* from DbUsers a  where 1=1 " + where ;
			return SqlHelper.Select(sql);
		}
		
		public static DataSet SelectForList(string where,int pageIndex,int pageSize,bool bCount,out int count )
		{
			count = 0;
						
			if (where.Trim().ToLower().StartsWith("where"))
				where = where.Substring(5);
			if (!where.Trim().ToLower().StartsWith("and") && where.Trim().Length > 0)
				where = " and " + where;
			string sql = "select a.* from dbUsers a where 1=1 " + where;
                
			return SqlHelper.Select(sql,pageSize,pageIndex,bCount,ref count);
		}		
		public static DataSet Select(string where,int pageIndex,int pageSize,bool bCount,out int count )
		{
			count = 0;			
			if (where.Trim().ToLower().StartsWith("where"))
				where = where.Substring(5);
			if (!where.Trim().ToLower().StartsWith("and") && where.Trim().Length > 0)
				where = " and " + where;			
			string sql = "select a.* from DbUsers a  where 1=1 " + where;
			return SqlHelper.Select(sql,pageSize,pageIndex,bCount,ref count);
		}
		#endregion
	}
}
